package mssql;

import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.*;

import javax.imageio.ImageIO;

public class PopulateEmmaImages {

	public static void populateEmmaImages(String path_images, String server, String database, String image_table, String user_name, String password)
			throws IOException, ClassNotFoundException, SQLException {

		//String connectionUrl = "jdbc:sqlserver://utwks10760.ewi.utwente.nl\\SQLEXPRESS;Database=EmmaLibrary;user=puppy;password=puppyir";// +
		String connectionUrl = "jdbc:sqlserver://"+ server+";Database="+database+";user="+user_name+";password="+password;// +" +
		
		
		
		// "databaseName=HelloWorld;integratedSecurity=true;";
		// Declare the JDBC objects.
		java.sql.Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;

	//	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
	//	con = DriverManager.getConnection(connectionUrl);
		
		
		String files;
		File folder = new File(path_images);
		File[] listOfFiles = folder.listFiles();

		for (int i = 0; i < listOfFiles.length; i++) {

			if (listOfFiles[i].isFile()) {
				files = listOfFiles[i].getName();
				String without_ext = files.substring(0, files.lastIndexOf("."));
				System.out.println(files);
				System.out.println(without_ext);

				// createFile

				InputStream stream = new FileInputStream(listOfFiles[i]
						.getAbsolutePath());
				
				
				//byte[] b = new byte[(int) listOfFiles[i].length()];
				//stream.read(b);

				// create statement to add the following info

				// id , file name, binary file data

				// Build the insert statement
				// Create a parameterized query

				try {
					// Establish the connection.
					
					Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
					con = DriverManager.getConnection(connectionUrl);
					// Create and execute an SQL statement that returns some
					// data.
					 String Query = ("insert + "+image_table + "VALUES (?,?,?)");
					 PreparedStatement pstmt = con.prepareStatement(Query);
					 pstmt.setInt(2, Integer.valueOf(without_ext));
					 pstmt.setString(3, files);
					 pstmt.setBinaryStream(1, stream,listOfFiles[i].length()); //method to insert a stream of bytes

					 //execute the insert statement
					 pstmt.executeUpdate(); 
					 
					 System.out.println("Inserting "+ "\t" + files);
					
				
				}

				// Handle any errors that may have occurred.
				catch (Exception e) {
					e.printStackTrace();
				}

				finally {
					if (rs != null)
						try {
							rs.close();
						} catch (Exception e) {
						}
					if (stmt != null)
						try {
							stmt.close();
						} catch (Exception e) {
						}
					if (con != null)
						try {
							con.close();
						} catch (Exception e) {
						}
				}
			}

		}

	}
	
	
	public static void storeImage(InputStream in , String path_file) throws IOException{
		
		 OutputStream out=new FileOutputStream(path_file);
		  byte buf[]=new byte[1024];
		  int len;
		  while((len=in.read(buf))>0)
		  out.write(buf,0,len);
		  out.close();
		  in.close();
		
		
		
	}
	
	public static void recoverEmmaImages(String server,
			String database,  String user_name,
			String password) throws IOException, ClassNotFoundException,
			SQLException {

		// String connectionUrl =
		// "jdbc:sqlserver://utwks10760.ewi.utwente.nl\\SQLEXPRESS;Database=EmmaLibrary;user=puppy;password=puppyir";//
		// +
		String connectionUrl = "jdbc:sqlserver://" + server + ";Database="
				+ database + ";user=" + user_name + ";password=" + password;// +"
																			// +

		java.sql.Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			// Establish the connection.

			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			con = DriverManager.getConnection(connectionUrl);
			// Create and execute an SQL statement that returns some
			// data.
			String query = ("SELECT EkbID,afbeelding FROM dbo.EmmaKinderboekenImage_tbl ; ");

			Statement statement = con.createStatement();
			rs = statement.executeQuery(query);

			// return query result
			while (rs.next()) {
				// display content from column "name"
			
				int id=  rs.getInt("EkbID");
				Blob temp1 = rs.getBlob("afbeelding");
				System.out.println("Reading image  " + id);
			
				storeImage(temp1.getBinaryStream(), "/home/sergio/emma_images/" + id + ".jpg");
				

			}
			con.close();

		}

		// Handle any errors that may have occurred.
		catch (Exception e) {
			e.printStackTrace();
		}

		finally {
			if (rs != null)
				try {
					rs.close();
				} catch (Exception e) {
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (Exception e) {
				}
			if (con != null)
				try {
					con.close();
				} catch (Exception e) {
				}
		}
	}
	
	public static void queryEmma() throws IOException,
			ClassNotFoundException, SQLException {

		String connectionUrl = "jdbc:sqlserver://utwks10760.ewi.utwente.nl\\SQLEXPRESS;Database=EmmaLibrary;user=puppy;password=puppyir";// +
		// "databaseName=HelloWorld;integratedSecurity=true;";
		// Declare the JDBC objects.
		java.sql.Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;

	
		try {
			// Establish the connection.

			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			con = DriverManager.getConnection(connectionUrl);
			// Create and execute an SQL statement that returns some
			// data.
			String query = ("SELECT Titel, Annotatie FROM EmmaLibrary.dbo.emmakinderboeken WHERE FREETEXT(*, 'kanker')	; ");

			Statement statement = con.createStatement();
			rs = statement.executeQuery(query);

			// return query result
			while (rs.next()){
				// display content from column "name"
				System.out.println("MS-SQL Query result: "
						+ rs.getString("Titel"));

			}
			con.close();

	

		}

		// Handle any errors that may have occurred.
		catch (Exception e) {
			e.printStackTrace();
		}

		finally {
			if (rs != null)
				try {
					rs.close();
				} catch (Exception e) {
				}
			if (stmt != null)
				try {
					stmt.close();
				} catch (Exception e) {
				}
			if (con != null)
				try {
					con.close();
				} catch (Exception e) {
				}
		}

	}
	
	
	
	
	
	 public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {

		 String path ="/home/sergio/Dropbox/puppy_emma/cover";
		 //populateEmmaImages(path);
		// queryEmma();
		 
		 
	//	 if(args.length!=6){
		//	
		//	 System.out.println("Usage: java -jar MSSQLConnection.PopulateEmmaImages path_images server database image_table username password ");
			 
		// }
		 
		 /*	String path_images= args[1];
			String server= args[2];
			String database=args[3];
			
			String image_table=args[4];puppy;password=puppyir
			
			String user_name=args[5];
			String password=args[6];*/
			
			
			String path_images="";
			String server= "utwks10760.ewi.utwente.nl\\SQLEXPRESS";
			String database="emma";
			
			String image_table="";
			
			String user_name="puppy";
			String password="puppyir";
			//populateEmmaImages(path_images,  server,  database, image_table,  user_name,password);
			
			recoverEmmaImages(server,database,user_name,password);
		
	 }
}

